﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GAMS.MODEL;

namespace KPIS.GERP.GAMS.DAL
{
    public sealed class D_ACM_Finance_Recive
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        public IList<M_ACM_Finance_Recive> GetListFullDataRecive(string strMonth, string strYear)
        {
            string strSQL;
            Decimal sum;
            DataRowCollection drc = null;
            IList<M_ACM_Finance_Recive> infoList = new List<M_ACM_Finance_Recive>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@month", strMonth);
                dbManager.AddParameters(1, "@year", string.IsNullOrEmpty(strYear) ? "0" : (Convert.ToInt32(strYear) - 543).ToString());
             

                strSQL = "select receipt_no,receive_date,vat_amount,remarks ,vat_amount as sumVatamount"
                    + " from fim_receives"
                    + " where receipt_no is not null"
                    + " AND receipt_no <> ''"
                    + " AND amount <> 0"
                    + " AND vat_amount <> 0"
                    + " AND receive_date is not null"
                    + (!string.IsNullOrEmpty(strMonth) ? " and MONTH(receive_date)=@month" : "")
                    + (!string.IsNullOrEmpty(strYear) ? " and YEAR(receive_date)=@year" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_Finance_Recive info = new M_ACM_Finance_Recive();
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receive_date = Convert.ToDateTime(drc[i]["receive_date"]);
                        info.vat_amount = Convert.ToInt32(drc[i]["vat_amount"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        sum = (Convert.ToDecimal(drc[i]["sumVatamount"]));
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_ACM_Finance_Recive> GetList()
        {
            string strSQL;
            Decimal sum;

            DataRowCollection drc = null;
            IList<M_ACM_Finance_Recive> infoList = new List<M_ACM_Finance_Recive>();
            try
            {
                dbManager.Open();

                strSQL = "select receipt_no,receive_date,vat_amount,remarks ,vat_amount as sumVatamount from fim_receives  where receipt_no <> " + "' NULL'"
                       + "' NULL'AND receipt_no <> ''  AND amount <> 0"
                       + " AND vat_amount <> 0  and receive_date <> ''";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_Finance_Recive info = new M_ACM_Finance_Recive();
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receive_date = Convert.ToDateTime(drc[i]["receive_date"]);
                        info.vat_amount = Convert.ToInt32(drc[i]["vat_amount"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        sum = (Convert.ToDecimal(drc[i]["sumVatamount"]));
                        infoList.Add(info);

                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public string SumRecivetaxAmount(string selectdate,string ProcessName)
        {
            try
            {
                //Decimal drc;
                string strSQL;
                //DataRowCollection drc = null;
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@selectdate", selectdate);
                if (selectdate != null && ProcessName == "SumRecivetaxAmount")
                {
                    strSQL = "select sum(vat_amount) as sumVatamount from fim_receives  where receipt_no <> "
                           + "' NULL'AND receipt_no <> ''  AND amount <> 0"
                           + " AND vat_amount <> 0  and receive_date <> '' AND MONTH(receive_date)= @selectdate";
                }
                else if (selectdate != null && ProcessName == "SumRecive")
                {
                    strSQL = "select sum(amount) as sumVatamount from fim_receives  where receipt_no <> "
                           + "' NULL'AND receipt_no <> ''  AND amount <> 0"
                           + " AND vat_amount <> 0  and receive_date <> '' AND MONTH(receive_date)= @selectdate";
                }
                else
                {
                    strSQL = "select sum(vat_amount) as sumVatamount from fim_receives  where receipt_no <> "
                           + "' NULL'AND receipt_no <> ''  AND amount <> 0"
                           + " AND vat_amount <> 0  and receive_date <> ''";
                }
               
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString().Length > 0 ? dt.Rows[0][0].ToString() : "0";
                }
                else
                {
                    return "0";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetValueCodeAcc(string acc_name)
        {
            try
            {
                //Decimal drc;
                string strSQL;
                //DataRowCollection drc = null;
                dbManager.Open();
                dbManager.CreateParameters(1);


                dbManager.AddParameters(0, "@AccName", acc_name);
                
                strSQL = "select  account_seq from acm_mas_account where  account_name  LIKE '%'+@AccName+'%'";
               

                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
                else
                {
                    return "0";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
           
        }

        public IList<M_ACM_Finance_Recive> GetvalueAccPND(int? month, int? year, int? contact_type)
        {
           // Dictionary<string, decimal> ret = new Dictionary<string, decimal>();
            IList<M_ACM_Finance_Recive> ret = new List<M_ACM_Finance_Recive>();
            DataRowCollection drc = null;
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@Month", (month != null ? month : 0));
                dbManager.AddParameters(1, "@Year", (year != null ? year : 0));
                dbManager.AddParameters(2, "@Type", (contact_type != null ? contact_type : 0));

                strSQL = "SELECT fr.receive_seq,fr.receipt_no,fr.receive_date,fr.receive_type_seq,frt.receive_type_name"
                + " ,fr.contact_seq,case when imc.NameTh is null then imc.NameEng else imc.NameTh end as Name"
                + " ,imc.ConGrpId as ContactGroup,iml.LookUpName ContactGroupName"
                + " ,fr.amount,fr.vat_amount,fr.exclude_vat,fr.tax,fr.created_by,fr.created_when"
                + " FROM fim_receives fr"
                + " LEFT JOIN IcmMasContact imc ON imc.ConId = fr.contact_seq"
                + " LEFT JOIN IcmMasLookUp iml ON iml.LookUpId = imc.ConGrpId"
                + " LEFT JOIN fim_mas_receive_types frt ON frt.receive_type_seq = fr.receive_type_seq"
                + " WHERE fr.contact_seq is not null AND fr.amount is not null AND ( fr.vat_amount is not null OR fr.tax is not null) "
                + (month != null ? " AND MONTH(fr.receive_date) = @Month":"")
                + (year != null ? " AND YEAR(fr.receive_date)  = @Year":"")
                + (contact_type != null ? " AND imc.ConGrpId  = @Type":"")
                + "";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {                 
                        M_ACM_Finance_Recive item = new M_ACM_Finance_Recive();
                        //item.receive_seq = Convert.ToInt32(drc[i]["receive_seq"] == null ? 0 : drc[i]["receive_seq"]);
                        item.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        item.vat_amount = (drc[i]["vat_amount"] != null ? Convert.ToDecimal(drc[i]["vat_amount"]):0);
                        item.exclude_vat = (drc[i]["exclude_vat"] != null ? Convert.ToDecimal(drc[i]["exclude_vat"]):0);
                        ret.Add(item);
                    }
                   
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (dbManager.Connection != null) dbManager.Dispose();
            }

            return ret;
        }


         public DataTable ReportTaxSale(int? month,int? year)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@Strmonth", month);
                dbManager.AddParameters(1, "@stryear", year);

                strSQL = "select ROW_NUMBER()OVER(ORDER BY a.receive_seq DESC) AS Row,a.receive_date as date,a.receipt_no as receiptNo,(case a.contact_seq when 0 then b.NameTh else a.temp_contact end) as ConName,(b.TaxCode)as CodeCon,'-' as HQName,'-' as BranchName,(case a.vat_amount when 0 then 0 else a.amount-a.vat_amount end) as amount ,a.vat_amount as vatamount,( case a.vat_amount when 0 then 0 else a.amount END ) as IncludeVat,(case a.vat_amount when  0   then a.amount  else 0 END) as a  from fim_receives a  "
                         +" left join IcmMasContact b on a.contact_seq=b.ConId "
                         + " where a.record_status<>'D' and MONTH(a.receive_date) = @Strmonth and a.bgm_year=@stryear and a.receive_mode = 2";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }
        }
    
}
